from peewee import *
db = MySQLDatabase('nemtocr', host='localhost', port=3800, user='root', password='!@mtg23YWZ^&')


class Config(Model):
    id = PrimaryKeyField()
    year = IntegerField()
    batch = IntegerField()
    type = IntegerField()
    key = CharField()
    value = FloatField()
    sort = IntegerField()
    mark = TextField()
    is_deleted = IntegerField()

    class Meta:
        database = db  # This model uses the "spider" database.
        table_name = 'config'  # 如果不指定，默认是person

    # 插入单条数据(返回新增id)
    def toInsert(self, row):
        try:
            return Config.insert(row).execute()
        except Exception as e:
            return ''

    # 批量插入数据
    def toInsertBatch(self, rows):
        with db.atomic():
            Config.insert_many(rows).execute()

    # 根据id，获取单条记录
    def getOne(self, id):
        try:
            return Config.select().where(Config.id == id).get()
        except Exception as e:
            return ''

    # 根据id，获取记录
    def getRowsByKey(self, key):
        try:
            return Config.select(Config.year, Config.value).where(Config.key == key).order_by(
                Config.year.desc()).offset(0).limit(3).dicts().execute()
        except Exception as e:
            print(f'获取加权系数失败：{e}')
            return ''

    # 根据年份查询列表
    def getList(self, year):
        try:
            return Config.select().where(Config.year == year).dicts().execute()
        except Exception as e:
            return ''


    # 查近两年的控制线
    def getList2(self, year, batch, type, key):
        try:
            return Config.select().where((Config.year >= year) &
                                         (Config.batch == batch) &
                                         (Config.type == type) &
                                         (Config.key == key)
                                         ).order_by(Config.year.desc()).dicts().execute()
        except Exception as e:
            print(f'查询控制线失败：{e}')
            return ''


if __name__ == '__main__':
    config = Config()
    data = config.getRowsByKey('assign')
    for index, row in enumerate(data):
        print(row)
